﻿Imports System.Runtime.InteropServices
Imports System.Data.Objects
Imports System.IO
Imports System.Data
Imports VSModel
Imports Cls_Common

Partial Class Control_WareHouse_ImportData
	Inherits System.Web.UI.UserControl
	Private Shared ReadOnly log As log4net.ILog = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
#Region "PRIVATE FUNCTION"
	Private Sub RemoveEmptyRow(ByRef dt1 As DataTable)
		'Remove đi nhưng dòng dt1
		Dim _dt1 = dt1.Rows.Cast(Of DataRow)().Where(
		 Function(row) Not row.ItemArray.All(
		  Function(field) TypeOf field Is System.DBNull _
		   OrElse String.Compare(TryCast(field, String).Trim(), String.Empty) = 0) _
		  ).CopyToDataTable()
		dt1 = _dt1
		_dt1.Dispose()
	End Sub
#End Region
#Region "EVENT FOR CONTROL"
	Protected Sub btnProcess_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnProcess.Click
		If flDocument.HasFile Then
			'To save every file with different name
			Dim randamName As String = DateTime.Now.ToFileTime().ToString()
			Dim relativePath As String = Server.MapPath("~") & "\Uploads\"
			If Not Directory.Exists(relativePath) Then
				Directory.CreateDirectory(relativePath)
			End If
			'Complete path of the file.
			Dim FilePath As String = (relativePath & randamName) + flDocument.FileName

			Try
				' tiến hành upload file lên server
				flDocument.SaveAs(FilePath)

				'' Get Data from file 
				Dim dt As DataTable = New DataTable()
				dt = ReadExcelToDT(FilePath, 0, 0, "", 2)
				RemoveEmptyRow(dt1:=dt)

				Dim _totalRow As Integer = dt.Rows.Count

				Dim _rowEffect As Integer = 0
				If dt.Rows.Count > 0 Then
					Using data As New VSEntities
						For index = 0 To dt.Rows.Count - 1
							Try
								Dim wh As New WareHouse
								With wh
									.ItemCode = dt.Rows(index)(0).ToString
									.Source = dt.Rows(index)(1).ToString
									.Pallet = dt.Rows(index)(2).ToString
									.BUN = dt.Rows(index)(3).ToString
									.PackingSize = dt.Rows(index)(4).ToString
									.PackingType = dt.Rows(index)(5).ToString
									.BatchNo = dt.Rows(index)(6).ToString
									.VendorCode = dt.Rows(index)(7).ToString
									.StatusId = 1
									.Created = Now
									.Creator = Session("UserName")

								End With
								'loai nhung item trung batch no va ko co item va vendor
								If dt.Rows(index)(0).ToString <> "" AndAlso dt.Rows(index)(7).ToString <> "" AndAlso Not checkExistBatchNo(dt.Rows(index)(6).ToString) Then
									data.WareHouses.AddObject(wh)
									data.SaveChanges()
									_rowEffect = _rowEffect + 1
									'update here
								End If
							Catch ex As Exception
							End Try
						Next


					End Using
				End If
				Excute_Javascript(String.Format("AlertboxRedirect('Insert successful {0}/{1} rows.','../../Page/WareHouse/List.aspx');", _rowEffect, _totalRow), Me.Page, True)
			Catch ex As Exception
				log4net.Config.XmlConfigurator.Configure()
				log.Error("Error error " & AddTabSpace(1) & Session("Username") & AddTabSpace(1) & "IP:" & GetIPAddress(), ex)
				txtFileUploadName.Text = ""
				Excute_Javascript("Alertbox('error while processing data !');", Me.Page, True)
			Finally
				'' final-> Delete file upload
				If File.Exists(FilePath) Then
					File.Delete(FilePath)
				End If
			End Try
		End If
	End Sub
	Function checkExistBatchNo(ByVal _BatchNo As String) As Boolean
		Using data As New VSEntities
			Dim p = (From q In data.WareHouses Where q.BatchNo = _BatchNo Select q).FirstOrDefault
			If Not p Is Nothing Then
				Return True
			End If
		End Using
		Return False
	End Function

#End Region

#Region "Apose read"
	Public Function ReadExcelToDT(ByVal strFilePath As String, ByVal intBeginRow As Integer, ByVal intBeginCol As Integer, ByRef strErr As String, Optional ByVal iRowEndVal As Integer = 0, Optional ByVal iColEndVal As Integer = 0) As DataTable
		If Not File.Exists(strFilePath) Then
			strErr = "FileNotFound"
		End If
		Dim wb As New Aspose.Cells.Workbook()
		wb.Open(strFilePath)
		Dim ws As Aspose.Cells.Worksheet = wb.Worksheets(0)

		Dim dt As New DataTable()
		'------init row,col-------------------------
		Dim iRowEnd As Integer = iRowEndVal
		Dim iColEnd As Integer = iColEndVal
		'If iRowEndVal > 0 Then
		iRowEnd = GetEndRow(intBeginRow, intBeginCol, ws)
		'End If
		' If iColEndVal > 0 Then
		iColEnd = GetEndCol(intBeginRow, intBeginCol, ws)
		'End If
		'-------------------------------------------
		Try
			'get Header
			For j As Integer = intBeginCol To iColEnd - 1
				Dim strDataColumn As String = ws.Cells(intBeginRow, j).Value.ToString().Trim()
				dt.Columns.Add(New DataColumn(strDataColumn, GetType(String)))
			Next
			'get Data
			Dim intStep As Integer = 0
			For i As Integer = intBeginRow + 1 To iRowEnd - 1
				intStep = 0
				Dim dr As DataRow = dt.NewRow()
				For j As Integer = intBeginCol To iColEnd - 1
					Dim strValue As String = If(ws.Cells(i, j).Value Is Nothing, "", ws.Cells(i, j).Value.ToString())
					dr(intStep) = strValue
					intStep += 1
				Next
				dt.Rows.Add(dr)
			Next
			Return dt
		Catch exp As Exception
			Return Nothing
		End Try
	End Function
	Public Function GetEndCol(ByVal intBeginRow As Integer, ByVal intBeginCol As Integer, ByVal ws As Aspose.Cells.Worksheet) As Integer
		Try
			Dim intCol As Integer = intBeginCol
			While True
				If ws.Cells(intBeginRow, intCol).Value Is Nothing _
						OrElse ws.Cells(intBeginRow, intCol).Value.ToString() = "" Then
					Exit While
				Else
					intCol += 1
				End If
			End While
			Return intCol
		Catch ex As Exception
			Return 0
		End Try
	End Function
	Public Function GetEndRow(ByVal intBeginRow As Integer, ByVal intBeginCol As Integer, ByVal ws As Aspose.Cells.Worksheet) As Integer
		Try
			Dim intRow As Integer = intBeginRow + 1	' +1 --> bo Header
			While True
				If ws.Cells(intRow, intBeginCol).Value Is Nothing _
					OrElse ws.Cells(intRow, intBeginCol).Value.ToString() = "" Then
					Exit While
				Else
					intRow += 1
				End If
			End While
			Return intRow
		Catch ex As Exception
			Return 0
		End Try
	End Function
#End Region


	Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
		If Not IsPostBack Then
			hplFileTemplate.NavigateUrl = MainDomain & "Template/WH_template.xlsx"
		End If
	End Sub
End Class
